//! Database service for managing database connections and operations
//! 
//! This module provides a high-level interface for database operations.

use crate::{Error, Result};
use sea_orm::{Database, DatabaseConnection, ConnectOptions, ConnectionTrait};
use std::sync::Arc;
use std::time::Duration;
use tracing::{info, warn};

/// Database service for managing connections and operations
#[derive(Debug, Clone)]
pub struct DatabaseService {
    connection: Arc<DatabaseConnection>,
}



impl DatabaseService {
    /// Create a new database service with the given connection URL
    pub async fn new(database_url: &str) -> Result<Self> {
        let mut opt = ConnectOptions::new(database_url);
        opt.max_connections(100)
            .min_connections(5)
            .connect_timeout(Duration::from_secs(30))
            .idle_timeout(Duration::from_secs(600))
            .sqlx_logging(true);

        let connection = Database::connect(opt).await?;
        
        info!("Database connection established successfully");
        
        Ok(Self { connection: Arc::new(connection) })
    }
    
    /// Create a database service from an existing connection
    pub fn from_connection(connection: DatabaseConnection) -> Self {
        Self { connection: Arc::new(connection) }
    }
    
    /// Get the database connection
    pub fn connection(&self) -> &DatabaseConnection {
        &self.connection
    }
    
    /// Get the database connection as Arc
    pub fn connection_arc(&self) -> Arc<DatabaseConnection> {
        Arc::clone(&self.connection)
    }
    
    /// Test the database connection
    pub async fn test_connection(&self) -> Result<()> {
        use sea_orm::ConnectionTrait;
        
        let result = self.connection.ping().await;
        match result {
            Ok(_) => {
                info!("Database connection test successful");
                Ok(())
            }
            Err(e) => {
                warn!("Database connection test failed: {}", e);
                Err(Error::Database(e))
            }
        }
    }
    
    /// Get database version information
    pub async fn get_version(&self) -> Result<String> {
        use sea_orm::{ConnectionTrait, Statement};
        
        let version_query = match self.connection.get_database_backend() {
            sea_orm::DatabaseBackend::Postgres => "SELECT version()",
            sea_orm::DatabaseBackend::MySql => "SELECT VERSION()",
            sea_orm::DatabaseBackend::Sqlite => "SELECT sqlite_version()",
        };
        
        let result = self.connection
            .query_one(Statement::from_string(
                self.connection.get_database_backend(),
                version_query.to_string(),
            ))
            .await?;
        
        if let Some(row) = result {
            let version: String = row.try_get("", "version").unwrap_or_default();
            Ok(version)
        } else {
            Err(Error::internal("Failed to get database version"))
        }
    }
    
    /// Get database type as string
    pub fn get_database_type(&self) -> String {
        match self.connection.get_database_backend() {
            sea_orm::DatabaseBackend::Postgres => "PostgreSQL".to_string(),
            sea_orm::DatabaseBackend::MySql => "MySQL".to_string(),
            sea_orm::DatabaseBackend::Sqlite => "SQLite".to_string(),
        }
    }
    
    /// Close the database connection
    pub async fn close(self) -> Result<()> {
        // Try to get the inner connection if we're the only owner
        match Arc::try_unwrap(self.connection) {
            Ok(conn) => {
                conn.close().await?;
                info!("Database connection closed");
                Ok(())
            }
            Err(_) => {
                warn!("Cannot close database connection: still has other references");
                Ok(())
            }
        }
    }
}

#[cfg(test)]
mod tests {
    use super::*;
    use sea_orm::MockDatabase;
    
    #[tokio::test]
    async fn test_database_service_creation() {
        let db = MockDatabase::new(sea_orm::DatabaseBackend::Postgres)
            .into_connection();
        
        let service = DatabaseService::from_connection(db);
        assert_eq!(service.get_database_type(), "PostgreSQL");
    }
    
    #[tokio::test]
    async fn test_database_type_detection() {
        let postgres_db = MockDatabase::new(sea_orm::DatabaseBackend::Postgres)
            .into_connection();
        let postgres_service = DatabaseService::from_connection(postgres_db);
        assert_eq!(postgres_service.get_database_type(), "PostgreSQL");
        
        let mysql_db = MockDatabase::new(sea_orm::DatabaseBackend::MySql)
            .into_connection();
        let mysql_service = DatabaseService::from_connection(mysql_db);
        assert_eq!(mysql_service.get_database_type(), "MySQL");
        
        let sqlite_db = MockDatabase::new(sea_orm::DatabaseBackend::Sqlite)
            .into_connection();
        let sqlite_service = DatabaseService::from_connection(sqlite_db);
        assert_eq!(sqlite_service.get_database_type(), "SQLite");
    }
}